﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Xml;
using System.Windows.Forms;
using System.Net;
using System.Security.Cryptography.X509Certificates;
using System.Net.Security;
using System.Data.SqlServerCe;
using System.Diagnostics;

namespace Investafinance.Sync
{
    internal class Connector
    {
        // строка конекта к бызе данных
        private  string scon ;

        public Connector(string clectionFieldInSharePoint, string sConnectionString)
        {
            this.scon = sConnectionString;
            maskDefaultFild = clectionFieldInSharePoint;
        }

        private string GetXmlToString(XmlNodeList NodeList)
        {
            StringBuilder str = new StringBuilder("");
            foreach (XmlNode Node in NodeList)
            {
                //ows_
                if (Node.Attributes != null)
                {
                    str.Append("<Table>");
                    if (Node.Name == "Field")
                    foreach (XmlAttribute atribut in Node.Attributes)
                    {
                        if (StringInMaskString(atribut.Name,maskAtribut))
                            {
                                str.Append("<" + atribut.Name + ">");
                                str.Append(atribut.Value);
                                str.Append("</" + atribut.Name + ">");
                            }
                    }
                    str.Append("</Table>");
                }
                if (Node.HasChildNodes)
                    str.Append(GetXmlToString(Node.ChildNodes));
            }
            return (str.ToString());
        }

        private string maskAtribut = ",ID,Type,Name,SourceID,DisplayName,";

        private string maskDefaultFild = ",ID,FullName," +
                                         "JobTitle," +
                                         "WorkCity,OrganizationalIDNumber,GovernmentIDNumber,Email,Nickname,UserField2,Email2,UserField3,UserField4,Body,owshiddenversion,";

        private bool StringInMaskString(string s,string mask)
        {
            if (mask.IndexOf("," + s + ",") != -1)
                return true;
            else
                return false;
        }

        public void ConnectList(string Url, Credential user)
        {
            try
            {
                ServicePointManager.ServerCertificateValidationCallback +=
                    delegate(Object sender, X509Certificate cert,
                    X509Chain chain, SslPolicyErrors sslerr) { return true; };

                this.client = new conect.Lists();
                this.client.Url = Url;
                
                //@"https://ict.ctradeorg.net:25464/_vti_bin/Lists.asmx"; 
                //"https://sd.kr.investafinance.ru/Lists/_vti_bin/Lists.asmx";
                //https://ict.ctradeorg.net:25464/Lists/_vti_bin/Lists.asmx
                //http://investafin-wss/_vti_bin/Lists.asmx


                if (user != null)
                    if (
                        (user.Domian != null) && (user.Domian != "") &&
                        (user.Password != null) && (user.Password != "") &&
                        (user.UserName != null) && (user.UserName != "")
                       )
                    {
                        this.client.Credentials = new System.Net.NetworkCredential(user.UserName, user.Password, user.Domian);
                    }
                    else
                    {
                        System.Windows.Forms.MessageBox.Show("Not filled fields authentication");
                        //если это не так предложить пользователю заполнить 
                    }
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show("error: \n"+ex.Message);
            }
        }

        public Investafinance.conect.Lists client = null;
        public DataSet ds = null;
        public string currentToken = null;
        public string listNameHash = null;
        public string listURL = null;
        public string listName = null;
        public string viewHash = null;

        public bool IsUpdatingDataSet = false;

        public void GetDataSetFromSharePoint(string listURlVar, string listName, Credential user)
        {
            GC.Collect();
            GC.WaitForPendingFinalizers();
            this.currentToken = null;
            this.listNameHash = null;
            this.listURL = null;
            this.listName = listName;

            //загрузим из access файла 
            SqlCeConnection conn = new SqlCeConnection(scon);
            try
            {

                this.listURL = listURlVar;
                ConnectList(this.listURL, user);

                SqlCeCommand cmd;
                //открываем подключение 
                conn.Open();
                cmd = new SqlCeCommand("select * from Sync where TableName='MetaData_" + listName + "' ;", conn);
                SqlCeDataReader dr = cmd.ExecuteReader();
                cmd.Dispose();
                cmd = null;

                XmlDocument docViewFields = null;
                string currentChangeToken; 
                if (dr.Read())
                {
                    currentToken = dr["ChangeToken"].ToString();
                    currentChangeToken = dr["ChangeToken"].ToString();
                    listNameHash = dr["TableNameHash"].ToString();
                    viewHash = dr["viewHash"].ToString();
                    listURL = dr["URL"].ToString();

                    cmd = new SqlCeCommand("select * from MetaData_" + listName + ";", conn);
                    dr = cmd.ExecuteReader();
                    cmd.Dispose();
                    cmd = null;

                    string strViev = "<ViewFields>";
                    while (dr.Read())
                    {
                        strViev += "<FieldRef Name='" + dr["Name"].ToString() + "' />";
                    }
                    strViev += "</ViewFields>";

                    docViewFields = new XmlDocument();
                    docViewFields.LoadXml(strViev);
                }
                else
                {

                    currentChangeToken = null;
                   
                    XmlNode getListName = client.GetListAndView(this.listName, "");
                    this.listNameHash = getListName.ChildNodes[0].Attributes["Name"].Value;
                    this.viewHash = getListName.ChildNodes[1].Attributes["Name"].Value;
                }

                XmlNode docXml = client.GetListItemChangesSinceToken(this.listNameHash, null, null, docViewFields, null,
                                                                             null, currentChangeToken, null);

                if (docXml.ChildNodes[1].Attributes != null)
                    currentToken = docXml.ChildNodes[1].Attributes["LastChangeToken"].Value;

                bool flagIsShema = false;
                if (currentChangeToken != null)
                {
                    //посмотреть методанные 
                    //сформировать флаг
                    flagIsShema = ((XmlElement)docXml).GetElementsByTagName("Fields").Count != 0;
                }
                else
                {
                    flagIsShema = true;
                }

                if (flagIsShema)
                {
                    if (currentChangeToken != null)
                    {
                        try
                        {
                            cmd = new SqlCeCommand("DROP TABLE Sync_" + listName + " ;", conn);
                            cmd.ExecuteNonQuery();
                            cmd.Dispose();
                            cmd = null;
                            GC.Collect();
                            GC.WaitForPendingFinalizers();
                            cmd = new SqlCeCommand("DROP TABLE MetaData_" + listName + " ;", conn);
                            cmd.ExecuteNonQuery();
                            cmd.Dispose();
                            cmd = null;
                            GC.Collect();
                            GC.WaitForPendingFinalizers();
                        }
                        catch (Exception ex)
                        {
                            Debug.Assert(false, "Connector.cs GetDataSetFromSharePoint()" + "\n" + ex.Message + "\n -->" + ex.Source + "\n"); 
                        }
                    }
                    else
                    {
                        try
                        {
                            cmd = new SqlCeCommand("DROP TABLE MetaData_" + listName + " ;", conn);
                            cmd.ExecuteNonQuery();
                            cmd.Dispose();
                            cmd = null;
                            GC.Collect();
                            GC.WaitForPendingFinalizers();
                        }
                        catch (Exception ex)
                        {
                            if(ex.Message.IndexOf("The specified table does not exist")==-1)
                                Debug.Assert(false, "Connector.cs GetDataSetFromSharePoint()" + "\n" + ex.Message + "\n -->" + ex.Source + "\n"); 
                        }

                    }
                    //инициализируем Command 

                    cmd = new SqlCeCommand("CREATE TABLE MetaData_" + listName + " (Name nvarchar(255) not null, Type nvarchar(255), ID nvarchar(255), SourceID nvarchar(255));", conn);
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    cmd = null;
                    GC.Collect();
                    GC.WaitForPendingFinalizers();

                    string ss = GetXmlToString(docXml.ChildNodes);
                    ss = ss.Replace("<Table></Table>", "");
                    System.IO.StringReader xmlSR = new System.IO.StringReader("<XmlDS>" + ss + "</XmlDS>");
                    DataSet ds = new DataSet();
                    ds.ReadXml(xmlSR);

                    DataRow[] RowIn = ds.Tables["Table"].Select("ID not in ('-1')");


                    DialogFields df = new DialogFields();
                    df.WindowState = FormWindowState.Maximized;
                    df.Text = "Выбор синхранизируемых полей";

                    int i = 0;
                    int j = 0;
                    int bufer = 0;
                    int k = 0;
                    string s = "";
                    foreach (DataRow drow in RowIn)
                    {
                        k++;
                        i++;
                        df.ListCheck.Add(new CheckBox());
                        df.ListCheck[df.ListCheck.Count - 1].AutoSize = true;
                        df.ListCheck[df.ListCheck.Count - 1].Location = new System.Drawing.Point(20 + 300 * j, i * 20);
                        df.ListCheck[df.ListCheck.Count - 1].Name = drow["Name"].ToString().Trim();
                        s += df.ListCheck[df.ListCheck.Count - 1].Name+" ,";
                        df.ListCheck[df.ListCheck.Count - 1].Size = new System.Drawing.Size(80, 17);
                        df.ListCheck[df.ListCheck.Count - 1].TabIndex = k;
                        df.ListCheck[df.ListCheck.Count - 1].Text = drow["Name"].ToString().Trim() + "(" + drow["DisplayName"].ToString() + ")";
                        df.ListCheck[df.ListCheck.Count - 1].UseVisualStyleBackColor = true;
                        df.ListCheck[df.ListCheck.Count - 1].Checked = (StringInMaskString(drow["Name"].ToString().Trim(), maskDefaultFild));
                        df.Controls.Add(df.ListCheck[df.ListCheck.Count - 1]);
                        if (i == 10 + bufer)
                        {
                            j++;
                            if (j == 4)
                            {
                                j = 0;
                                bufer = i + 1;
                                i++;
                            }
                            else
                                i = bufer;
                        }
                    }//AAA/CCC
                    
                   // df.ShowDialog();
                    string sInsert = "";
                    foreach (DataRow drow in RowIn)
                    {
                        if (((CheckBox)df.Controls[drow["Name"].ToString().Trim()]).Checked)
                        {
                            sInsert = "insert into MetaData_" + listName + " (Name,Type,ID,SourceID) values ('"
                                + drow["Name"].ToString().Trim() + "'," +
                            "'" + drow["Type"].ToString().Trim() + "'," +
                            "'" + drow["ID"].ToString().Trim() + "'," +
                            "'" + drow["SourceID"].ToString().Trim() + "'); ";
                            cmd = new SqlCeCommand(sInsert, conn);
                            cmd.ExecuteNonQuery();
                            cmd.Dispose();
                            cmd = null;
                            GC.Collect();
                            GC.WaitForPendingFinalizers();
                        }
                    }
                    
                    //Тута надо сделать таблицу в access на основе сформированных мета данных!!!!
                    cmd = new SqlCeCommand("select * from MetaData_" + listName + ";", conn);
                    dr = cmd.ExecuteReader();
                    cmd.Dispose();
                    cmd = null;
                    string strCreateTableSQL = "CREATE TABLE Sync_" + listName + "(";
                    string strViev = "<ViewFields>";
                    string strMaskInTable = ",";
                    string strXmlTemplateFild = "";
                    while (dr.Read())
                    {
                        switch (dr["Type"].ToString())
                        {
                            case "Text":
                                strCreateTableSQL += dr["Name"].ToString() + " nvarchar(255), ";
                                break;
                            case "Integer":
                                strCreateTableSQL += dr["Name"].ToString() + " int, ";
                                break;
                            case "Number":
                                strCreateTableSQL += dr["Name"].ToString() + " int, ";
                                break;
                            case "DateTime":
                                strCreateTableSQL += dr["Name"].ToString() + " nvarchar(20), ";//" DateTime, ";
                                break;
                            case "Boolean":
                                strCreateTableSQL += dr["Name"].ToString() + " nvarchar(5), ";
                                break;
                            default:
                                strCreateTableSQL += dr["Name"].ToString() + " nvarchar(500), ";
                                break;
                        }
                        strViev += "<FieldRef Name='" + dr["Name"].ToString() + "' />";
                        strMaskInTable += dr["Name"].ToString() + ",";
                        strXmlTemplateFild += "<" + dr["Name"].ToString() + ">" + "</" + dr["Name"].ToString() + ">";
                    }
                    strXmlTemplateFild ="<Table>"+ strXmlTemplateFild+"</Table>";
                    strMaskInTable += ",";
                    strViev += "</ViewFields>";
                    strCreateTableSQL += " Modify nvarchar(10), EntryIDInOutlook nvarchar(255)"; //strCreateTableSQL.Remove(strCreateTableSQL.Length - 2);
                    strCreateTableSQL += ");";
                    cmd = new SqlCeCommand(strCreateTableSQL, conn);
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    cmd = null;

                    //Тут Заполняем таблицу  Sync_" + listName
                    docViewFields = new XmlDocument();
                    docViewFields.LoadXml(strViev);

                    XmlNode ndListItems = client.GetListItems(listName, null, null, docViewFields, "100000", null, null);

                    XmlNodeList nodeList = ndListItems.SelectNodes("/*/*");
                    string strXmlFildAndValue = "";
                    string XMLRezalt = "";
                    foreach (XmlNode Node in nodeList)
                    {
                        //ows_
                        strXmlFildAndValue = strXmlTemplateFild;
                        if (Node.Attributes != null)
                        {
                            foreach (XmlAttribute atribut in Node.Attributes)
                            {
                                string NameTeg = atribut.Name.Replace("ows_", "");
                                if (StringInMaskString(NameTeg, strMaskInTable))
                                {
                                    string value = atribut.Value.Replace(">", "&gt;");
                                    value = value.Replace("<", "&lt;");
                                    //тут надо убить теги в atribut.Value а то может получиться нехорошо!!!
                                    strXmlFildAndValue = strXmlFildAndValue.Replace("<" + NameTeg + ">", "<" + NameTeg + ">" + value);
                                }
                            }
                        }
                        if (strXmlFildAndValue != strXmlTemplateFild)
                        XMLRezalt += strXmlFildAndValue;
                    }
                    if (XMLRezalt != "")
                    {
                        //то загрузим схему и данные
                        xmlSR = new System.IO.StringReader("<XmlDS>" + XMLRezalt + "</XmlDS>");
                        this.ds = new DataSet();
                        this.ds.ReadXml(xmlSR);    
                    }
                    else
                    {
                        //то загрузим только схему
                        xmlSR = new System.IO.StringReader("<XmlDS>" + strXmlTemplateFild + "</XmlDS>");
                        this.ds = new DataSet();
                        this.ds.ReadXmlSchema(xmlSR);
                    }
                    IsUpdatingDataSet = false;
                }
                else 
                { 
                    //тут мы просто формируем  DataSet
                    //1 составим шаблон 
                    cmd = new SqlCeCommand("select * from MetaData_" + listName + ";", conn);
                    dr = cmd.ExecuteReader();
                    cmd.Dispose();
                    cmd = null;

                    string strXmlTemplateFild = "";
                    string strMaskInTable = ",";
                    while (dr.Read())
                    {
                        strXmlTemplateFild += "<" + dr["Name"].ToString() + ">" + "</" + dr["Name"].ToString() + ">";
                        strMaskInTable += dr["Name"].ToString() + ",";
                    }
                    strXmlTemplateFild = "<Table>" + strXmlTemplateFild + "</Table>";
                    strMaskInTable += ",";

                    //а теперь составим схему и данные
                    XmlNodeList nodeList = docXml.SelectNodes("/*/*");
                    string strXmlFildAndValue = "";
                    string XMLRezalt = "";
                    foreach (XmlNode Node in nodeList)
                    {
                        //ows_
                        strXmlFildAndValue = strXmlTemplateFild;
                        if (Node.Attributes != null)
                        {
                            string owshiddenversion = "";
                            string Id = "";
                            foreach (XmlAttribute atribut in Node.Attributes)
                            {
                                string NameTeg = atribut.Name.Replace("ows_", "");
                                if (StringInMaskString(NameTeg, strMaskInTable))
                                {
                                    //тут надо убить теги в atribut.Value а то может получиться нехорошо!!!&lt;
                                    string value = atribut.Value.Replace(">", "&gt;");
                                    value = value.Replace("<", "&lt;");
                                    strXmlFildAndValue = strXmlFildAndValue.Replace("<" + NameTeg + ">", "<" + NameTeg + ">" + value);
                                }
                                if (NameTeg == "owshiddenversion")
                                {
                                    owshiddenversion = atribut.Value.ToString().Trim();
                                }
                                else
                                if (NameTeg == "ID")
                                {
                                    Id = atribut.Value.ToString();
                                }
                            }
                          //  if (owshiddenversion != "1")
                          //  {
                                cmd = new SqlCeCommand("select * from Sync_" + listName + " where ID in ('" + Id + "','-1');", conn);
                                dr = cmd.ExecuteReader();
                                //cmd.Dispose();
                                //cmd = null;
                                //если вообще нет такой записи то ничего не делаем с этой записью тюе добавляем её в DataSet
                                if (dr.Read())
                                {
                                    //Если есть такая запись и  owshiddenversion её меньше чем у сервера то добавляем её в DataSet
                                    //Если   owshiddenversion = owshiddenversion сервера то нам в DataSet она ненужна недобавляем эту зпись вообще
                                    if (dr["Modify"].ToString() == "U")
                                    {
                                        if ((Convert.ToInt32(dr["owshiddenversion"]) - 1).ToString() == owshiddenversion)
                                        {
                                            strXmlFildAndValue = strXmlTemplateFild;
                                        }
                                    }
                                    else
                                    {
                                        if (dr["owshiddenversion"].ToString().Trim() == owshiddenversion)
                                        {
                                            //обнулим
                                            strXmlFildAndValue = strXmlTemplateFild;
                                        }
                                    }
                                }
                          //  }
                        }
                        if (strXmlFildAndValue != strXmlTemplateFild)
                            XMLRezalt += strXmlFildAndValue;
                    }
                    if (XMLRezalt != "")
                    {
                        //то загрузим схему и данные
                        System.IO.StringReader xmlSR = new System.IO.StringReader("<XmlDS>" + XMLRezalt + "</XmlDS>");
                        this.ds = new DataSet();
                        this.ds.ReadXml(xmlSR);
                    }
                    else
                    {
                        //то загрузим только схему
                        System.IO.StringReader xmlSR = new System.IO.StringReader("<XmlDS>" + strXmlTemplateFild + "</XmlDS>");
                        this.ds = new DataSet();
                        this.ds.ReadXmlSchema(xmlSR);
                    }
                    IsUpdatingDataSet = true;
                }

                /*
                    fix Up на случай если имеются поля с рускими имирами 
                    при выполнении this.ds.ReadXmlSchema(xmlSR)  происходит преобразование  
                    строки типо _x0442__x0435__x0441__x0442__x00 в тест_x00 
                    что приводит к глюку в дальнейшем !
                 */
                if (this.ds != null)
                {

                    string[] split = maskDefaultFild.Split(',');
                    for (int i = 0; i < this.ds.Tables[0].Columns.Count; i++)
                        for (int j = 0; j < split.Length; j++)
                            if (System.Xml.XmlConvert.DecodeName(split[j]) 
                                == this.ds.Tables[0].Columns[i].ColumnName)
                            {
                                this.ds.Tables[0].Columns[i].ColumnName = split[j];
                            }
                                    
                }



            }
            catch (Exception ex)
            {
                this.ds = null;
                IsUpdatingDataSet = false;

                if (ex.Message.IndexOf("The request failed with HTTP status 401: Unauthorized") !=-1)
                {
                    MessageBox.Show("Неверное имя (и/или) пароль");
                }
                else
                    if ((ex.Message.IndexOf("Unable to connect to the remote server") != -1) || (ex.Message.IndexOf("The remote name could not be resolved:") != -1) )
                    {
                        MessageBox.Show("Сервер недоступен. Повторите попытку позднее");
                    }
                    else
                         Debug.Assert(false, "Connector.cs GetDataSetFromSharePoint()" + "\n" + ex.Message + "\n -->" + ex.StackTrace + "\n"); 


            }
            finally
            {
                //закрываем подключение
                conn.Close();
                conn.Dispose();
                conn = null;
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
        }


        //обновление записи в таблице Sync
        public bool UpdateChangeToken()
        {
            GC.Collect();
            GC.WaitForPendingFinalizers();

            bool ret = true;
            SqlCeConnection conn = new SqlCeConnection(scon);
            try
            {
                conn.Open();

                SqlCeCommand cmd = new SqlCeCommand("UPDATE Sync SET  " +
                                                    "ChangeToken = '" + currentToken + "', " +
                                                    "TableNameHash = '" + listNameHash + "', " +
                                                    "URL = '" + listURL + "', " +
                                                    "viewHash ='" + viewHash + "' " +
                                        " where TableName  in ('MetaData_" + listName + "');", conn);
                if (cmd.ExecuteNonQuery() == 0)
                {
                    cmd = new SqlCeCommand("insert into Sync (ChangeToken,TableNameHash,URL,TableName,viewHash) values ('" + currentToken + "','"
                                                    + listNameHash + "','" + listURL + "','MetaData_" + listName + "','" + viewHash + "')", conn);
                    cmd.ExecuteNonQuery();
                }
                cmd.Dispose();
                cmd = null;
            }
            catch (Exception ex)
            {
                ret = false;
                Debug.Assert(false, "Connector.cs UpdateChangeToken()" + "\n" + ex.Message + "\n -->" + ex.Source + "\n"); 
            }
            finally
            {
                //закрываем подключение
                conn.Close();
                conn.Dispose();
                conn = null;
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }

            return ret;
        }
    }

    


    public partial class DialogFields : Form
    {
        public List<CheckBox> ListCheck = new  List<CheckBox>();
    }
}
